package kr.oss.cho.mapper;

import java.util.ArrayList;
import java.util.Map;

import kr.oss.cho.vo.SessionBrowserVO;
import kr.oss.cho.vo.SessionLockBrowserVO;
import kr.oss.cho.vo.SqlInjectionVO;
import kr.oss.cho.vo.SqlMonitorVO;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

public interface SqlVeiwMapper {
	 ArrayList<SqlMonitorVO> selectSqlMonitor(Map map);
	 Map<String, String> selectSessionCount();
	 ArrayList<SqlInjectionVO> selectSqlInjectionList();
	 ArrayList<SqlInjectionVO> selectSqlInjectionDetectList(Map map);
	 ArrayList<SessionBrowserVO> selectSessionBrowser(Map map);
	 ArrayList<SessionLockBrowserVO> selectSessionLockBrowser();
	 String selectCurrentSessionSqlText(Map map);
	 
	 @Update("ALTER SYSTEM KILL SESSION '${sid},${serial}'")
	 Integer alterCurrentSessionKill(Map map);
	 
	 @Update("UPDATE TN_SQLINJECTION SET SQL_CHECK = #{confirm}, SQL_CHECK_TIME = SYSDATE WHERE SQL_ID = #{sqlId}")
	 Integer updateConfirmSqlInjection(Map map);
	 
	 @Delete("DELETE FROM TN_SQLINJECTION WHERE SQL_ID = #{sqlId}")
	 Integer deleteConfirmSqlInjection(Map map);
	 
	 @Select("SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME ='TN_SQLINJECTION'")
	 Integer existTableTnSqlinjection();
	 
	 @Update("CREATE TABLE TN_SQLINJECTION 							"+
			"	(													"+
			"			       SQL_ID VARCHAR2(50) PRIMARY KEY,		"+
			"			       SQL_FULLTEXT CLOB NOT NULL,			"+
			"			       FETCHES NUMBER NOT NULL,				"+
			"			       EXECUTIONS NUMBER NOT NULL,			"+
			"			       ROWSEXEC NUMBER NOT NULL,			"+
			"			       PARSING_SCHEMA_NAME VARCHAR2(50) ,	"+
			"			       ADDRESS VARCHAR2(100) NOT NULL,		"+
			"			       HASH_VALUE VARCHAR2(100) NOT NULL,	"+
			"			       MODULE VARCHAR2(100) ,				"+
			"			       LAST_ACTIVE_TIME DATE,				"+
			"			       INJECTION VARCHAR2(500),				"+
			"			       SQL_CHECK CHAR(1) DEFAULT 'N' ,		"+ //N 체크안함, Y 위험하지 않음, W SQL INJECTION 공격
			"			       SQL_CHECK_TIME DATE 					"+
			"	)")
	 Integer createTableTnSqlinjection();
	 
	 Integer insertSqlInjection(SqlInjectionVO vo);
	 
	 @Select("SELECT COUNT(*) FROM TN_SQLINJECTION WHERE SQL_CHECK ='N'")
	 Integer countSqlInjection();
}
